# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymongo
import pymysql

class MongoPipeline(object):

    def __init__(self,mongo_url,mongo_db,collection):
        self.mongo_url = mongo_url
        self.mongo_db = mongo_db
        self.collection = collection

    @classmethod
    #from_crawler是一个类方法，由 @classmethod标识，是一种依赖注入的方式，它的参数就是crawler
    #通过crawler我们可以拿到全局配置的每个配置信息，在全局配置settings.py中的配置项都可以取到。
    #所以这个方法的定义主要是用来获取settings.py中的配置信息
    def from_crawler(cls,crawler):
        return cls(
            mongo_url=crawler.settings.get('MONGO_URL'),
            mongo_db = crawler.settings.get('MONGO_DB'),
            collection = crawler.settings.get('COLLECTION')
        )

    def open_spider(self,spider):
        self.client = pymongo.MongoClient(self.mongo_url)
        self.db = self.client[self.mongo_db]

    def process_item(self,item, spider):
        name = self.collection
        self.db[name].insert(dict(item))
        return item

    def close_spider(self,spider):
        self.client.close()

class PymysqlPipeline(object):
    def __init__(self,mysql_host,mysql_port,mysql_user,mysql_passwd,mysql_db):
        self.host= mysql_host
        self.port=mysql_port
        self.user=mysql_user
        self.passwd = mysql_passwd
        self.db=mysql_db

    @classmethod
    #host='localhost', user='root', password='123456', port=3306, db='test'
    def from_crawler(cls,crawler):
        return cls(
            mysql_host = crawler.settings.get('MYSQL_HOST'),
            mysql_port = crawler.settings.get('MYSQL_PORT'),
            mysql_user=crawler.settings.get('MYSQL_USER'),
            mysql_passwd = crawler.settings.get('MYSQL_PASSWD'),
            mysql_db = crawler.settings.get('MYSQL_DB')
        )

    def open_spider(self,spider):
        self.dbconn = pymysql.connect(host=self.host, user=self.user, password=self.passwd, port=self.port, db=self.db)
        self.dbcur = self.dbconn.cursor()

    def process_item(self,item, spider):
        items = dict(item)
        fund_list = []
        fund_list.append(items.get('product_id'))
        fund_list.append(items.get('date'))
        fund_list.append(float(items.get('net')))
        fund_list.append(float(items.get('totalnet')))
        fund_list.append(float(items.get('fqnet')))
        fund_list.append(float(items.get('inc')))
        fund_list.append(float(items.get('rate')))
        self.dyn_insert_sql('Fund_date',tuple(fund_list),self.dbconn,self.dbcur)

    def close_spider(self,spider):
        self.dbconn.close()

    def dyn_insert_sql(self,tablename, data, dbconn, cursor):
        tablename = tablename
        sql = "select GROUP_CONCAT(COLUMN_name,'') from information_schema.COLUMNS where table_name = %s ORDER BY ordinal_position "
        cursor.execute(sql, tablename)
        tup = cursor.fetchone()
        # 动态构造sql语句
        sql = 'INSERT INTO {table}({keys}) VALUES {values}'.format(table=tablename, keys=tup[0], values=data)
        # 使用try-except语句块控制事务的原子性
        try:
            if cursor.execute(sql):
                dbconn.commit()
        except:
            dbconn.rollback()